package com.magnusart.scrumify.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.magnusart.scrumppm.model.Builder;

public abstract class HsqlDbAbstactDAO <J extends Comparable<J>, T extends Comparable<T>, Y extends Builder> implements CommonDAO<J, T, Y>
{
	/**
	 * This factory instance is used to execute queries
	 */
	private HsqlDbDAOFactory factory;
	
	/**
	 * Prepared statement container
	 */
	private PreparedStatement pstmt;
	
	/**
	 * Place-holder for SQL-commands
	 */
	String sqlAdd;
	
	String sqlGetAll;
	
	String sqlGetOne;
	
	String sqlUpdate;

	String sqlDelete;
	
	private ResultSet rs;

	private Y builder;
	
	public HsqlDbAbstactDAO( HsqlDbDAOFactory factory ) {
		this.factory = factory;
	}
	
	final public T add( J parent, T entry ) {
		int addedID = -1;
		
		try  {
			pstmt = factory.getConnection().prepareStatement( sqlAdd );
			
			perpareAdd( parent, entry, pstmt );
			
			// Execute an update.
			int i = pstmt.executeUpdate();
			
			
			if( i > 0 ) {
				ResultSet rs = factory.getConnection().createStatement().executeQuery( "CALL IDENTITY()" );
				rs.next();
				
				addedID = rs.getInt( 1 );
			}
			
			// Close down statement.
			pstmt.close();

			// Close the connection.
			factory.closeConnection();
			
		} catch( SQLException sqle ) {
			sqle.printStackTrace();
			return null;
		}
		
		return addedEntry( factory, entry, builder, addedID );
	}


	@Override
	final public T delete( T entry ) {
		try  {
			pstmt = factory.getConnection().prepareStatement( sqlDelete );
			
			prepareDelete( entry, pstmt );
			
			// Execute an update.
			int i = pstmt.executeUpdate();
			
			
			// Close down the statement.
			pstmt.close();

			// Close the connection.
			factory.closeConnection();
			
			if ( i < 1 )
				return null;
			
		} catch( SQLException sqle ) {
			sqle.printStackTrace();
			return null;
		}
		
		return entry;
	}
	
	@Override
	final public List<T> getAll( J parent ) {
		ArrayList<T> entries = new ArrayList<T>();
		
		try  {
			pstmt = factory.getConnection().prepareStatement( sqlGetAll );
			
			prepareGetAll( parent, pstmt );
			
			// Execute an update
			rs = pstmt.executeQuery();
			
			// Close down the statement.
			pstmt.close();

			while( rs.next() ) {
				fetchBuilderData( entries, rs, builder );
			}
			
			// Close down the result set.
			rs.close();
			
			// Close the connection.
			factory.closeConnection();
			
		} catch( SQLException sqle ) {
			sqle.printStackTrace();
			return null;
		}

		
		// Sort this list before returning it.
		Collections.sort( entries );
		
		return entries;
	}
	
	@Override
	final public T update( J parent, T entry, Y builder) {
		
		T updated = null;
		
		try  {
			pstmt = factory.getConnection().prepareStatement( sqlUpdate );
			
			updated = pepareUpdate( parent, entry, builder,  pstmt );
			
			// Execute an update.
			int i = pstmt.executeUpdate();
			
			// Close down statement.
			pstmt.close();

			// Close the connection.
			factory.closeConnection();
			
			if ( i < 1 )
				return null;
			
		} catch( SQLException sqle ) {
			sqle.printStackTrace();
			return null;
		}
		
		return updated;
	}

	@Override
	final public T getOne( T entry ) {
		T fetched = null;
		
		try  {
			pstmt = factory.getConnection().prepareStatement( sqlGetOne );
			
			prepareGetOne( entry,  pstmt );
			
			// Execute an update.
			rs = pstmt.executeQuery();
			
			// Close down the statement.
			pstmt.close();

			// Close the connection.
			factory.closeConnection();

			if( rs.next() ) {
				fetched = fetchBuilderData( rs, builder );
			} else {
				return null;
			}
			
		} catch( SQLException sqle ) {
			sqle.printStackTrace();
			return null;
		}
		
		return fetched;
	}
	
	private void fetchBuilderData( List<T> entries, ResultSet rs, Y builder ) throws SQLException 
	{
		entries.add( fetchBuilderData( rs, builder ) );
	}

	abstract void prepareGetOne( T entry,  PreparedStatement pstmt  ) throws SQLException;
	
	abstract T pepareUpdate( J parent, T entry, Y builder,  PreparedStatement pstmt  ) throws SQLException;

	abstract void perpareAdd( J parent, T entry,  PreparedStatement pstmt )  throws SQLException;

	abstract void prepareDelete( T entry,  PreparedStatement pstmt ) throws SQLException;

	abstract void prepareGetAll( J parent,  PreparedStatement pstmt ) throws SQLException;

	abstract T fetchBuilderData( ResultSet rs, Y builder ) throws SQLException;

	abstract T addedEntry( DAOFactory factory, T entry, Y builder, int addedID);
}
